﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class RmsRollDao
    {
        public bool Delete(SqlConnection conn, int rollId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DELETE FROM [dbo].[Rms_Lend] ");
            builder.Append("WHERE ");
            builder.Append("\t[FileId] IN ");
            builder.Append("\t\t( ");
            builder.Append("\t\t\tSELECT [FileId] ");
            builder.Append("\t\t\tFROM ");
            builder.Append("\t\t\t\t[dbo].[Rms_File] ");
            builder.Append("\t\t\tWHERE ");
            builder.Append("\t\t\t\t[RollId] = @RollId\t\t\t ");
            builder.Append("\t\t) ");
            builder.Append("UPDATE [dbo].[Attachment] ");
            builder.Append("SET ");
            builder.Append("\t[DeleteFlag] = 1 ");
            builder.Append("WHERE ");
            builder.Append("\t[AttachmentId] IN ( ");
            builder.Append("\t\t\t\tSELECT [AttachmentId] ");
            builder.Append("\t\t\t\tFROM ");
            builder.Append("\t\t\t\t\t[dbo].[Rms_File] ");
            builder.Append("\t\t\t\tWHERE ");
            builder.Append("\t\t\t\t\t[RollId] = @RollId AND ISNULL([AttachmentId] ,'') <>'') ");
            builder.Append("DELETE FROM [dbo].[Rms_File] ");
            builder.Append("WHERE ");
            builder.Append("\t[RollId] = @RollId ");
            builder.Append("DELETE FROM [dbo].[Rms_Roll] ");
            builder.Append("WHERE ");
            builder.Append("\t[RollId] = @RollId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@RollId", SqlDbType.Int) };
            commandParameters[0].Value = rollId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public int GetFileNums(SqlConnection conn, int rollId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\tCount([FileId]) AS Nums ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Rms_File] ");
            builder.Append("WHERE ");
            builder.Append("\t[RollId] = @RollId ");
            SqlParameter[] commandParameters = new SqlParameter[30];
            commandParameters[0] = new SqlParameter("@RollId", SqlDbType.Int);
            commandParameters[0].Value = rollId;
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, builder.ToString(), commandParameters);
            if (obj2 != DBNull.Value)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public string GetRollName(SqlConnection conn, int rollId)
        {
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@RollId", SqlDbType.Int) };
            commandParameters[0].Value = Convert.ToInt32(rollId);
            string commandText = "SELECT [RollName] FROM [dbo].[Rms_Roll] WHERE [RollId] = @RollId ";
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, commandText, commandParameters);
            if (obj2 != null)
            {
                return obj2.ToString();
            }
            return string.Empty;
        }

        public int Insert(SqlConnection conn, RmsRollData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("INSERT INTO [dbo].[Rms_Roll] ");
            builder.Append("( ");
            builder.Append("\t[RoomId], ");
            builder.Append("\t[RollCode], ");
            builder.Append("\t[RollName], ");
            builder.Append("\t[Years], ");
            builder.Append("\t[BeginDate], ");
            builder.Append("\t[EndDate], ");
            builder.Append("\t[DeptId], ");
            builder.Append("\t[BZJG], ");
            builder.Append("\t[DeadLine], ");
            builder.Append("\t[Secret], ");
            builder.Append("\t[CategoryNo], ");
            builder.Append("\t[CatalogNo], ");
            builder.Append("\t[ArchiveNo], ");
            builder.Append("\t[BoxNo], ");
            builder.Append("\t[MicroNo], ");
            builder.Append("\t[CertificateKind], ");
            builder.Append("\t[CertificateStart], ");
            builder.Append("\t[CertificateEnd], ");
            builder.Append("\t[RollPage], ");
            builder.Append("\t[Remark], ");
            builder.Append("\t[ManageUser], ");
            builder.Append("\t[ReadUser], ");
            builder.Append("\t[AddUser], ");
            builder.Append("\t[AddTime], ");
            builder.Append("\t[ModUser], ");
            builder.Append("\t[ModTime], ");
            builder.Append("\t[DelUser], ");
            builder.Append("\t[DelTime], ");
            builder.Append("\t[Borrow], ");
            builder.Append("\t[Status] ");
            builder.Append(") ");
            builder.Append("VALUES ");
            builder.Append("( ");
            builder.Append("\t@RoomId, ");
            builder.Append("\t@RollCode, ");
            builder.Append("\t@RollName, ");
            builder.Append("\t@Years, ");
            builder.Append("\t@BeginDate, ");
            builder.Append("\t@EndDate, ");
            builder.Append("\t@DeptId, ");
            builder.Append("\t@BZJG, ");
            builder.Append("\t@DeadLine, ");
            builder.Append("\t@Secret, ");
            builder.Append("\t@CategoryNo, ");
            builder.Append("\t@CatalogNo, ");
            builder.Append("\t@ArchiveNo, ");
            builder.Append("\t@BoxNo, ");
            builder.Append("\t@MicroNo, ");
            builder.Append("\t@CertificateKind, ");
            builder.Append("\t@CertificateStart, ");
            builder.Append("\t@CertificateEnd, ");
            builder.Append("\t@RollPage, ");
            builder.Append("\t@Remark, ");
            builder.Append("\t@ManageUser, ");
            builder.Append("\t@ReadUser, ");
            builder.Append("\t@AddUser, ");
            builder.Append("\t@AddTime, ");
            builder.Append("\t@ModUser, ");
            builder.Append("\t@ModTime, ");
            builder.Append("\t@DelUser, ");
            builder.Append("\t@DelTime, ");
            builder.Append("\t@Borrow, ");
            builder.Append("\t@Status ");
            builder.Append(") ");
            builder.Append("SELECT SCOPE_IDENTITY() AS RetValue ");
            SqlParameter[] commandParameters = new SqlParameter[30];
            commandParameters[0] = new SqlParameter("@RoomId", SqlDbType.Int);
            commandParameters[0].Value = data.RoomId;
            commandParameters[1] = new SqlParameter("@RollCode", SqlDbType.NVarChar, 200);
            commandParameters[1].Value = data.RollCode;
            commandParameters[2] = new SqlParameter("@RollName", SqlDbType.NVarChar, 200);
            commandParameters[2].Value = data.RollName;
            commandParameters[3] = new SqlParameter("@Years", SqlDbType.NVarChar, 50);
            if (data.YearsIsNull)
            {
                commandParameters[3].Value = DBNull.Value;
            }
            else
            {
                commandParameters[3].Value = data.Years;
            }
            commandParameters[4] = new SqlParameter("@BeginDate", SqlDbType.DateTime);
            if (data.BeginDateIsNull)
            {
                commandParameters[4].Value = DBNull.Value;
            }
            else
            {
                commandParameters[4].Value = data.BeginDate;
            }
            commandParameters[5] = new SqlParameter("@EndDate", SqlDbType.DateTime);
            if (data.EndDateIsNull)
            {
                commandParameters[5].Value = DBNull.Value;
            }
            else
            {
                commandParameters[5].Value = data.EndDate;
            }
            commandParameters[6] = new SqlParameter("@DeptId", SqlDbType.Int);
            if (data.DeptIdIsNull)
            {
                commandParameters[6].Value = DBNull.Value;
            }
            else
            {
                commandParameters[6].Value = data.DeptId;
            }
            commandParameters[7] = new SqlParameter("@BZJG", SqlDbType.NVarChar, 100);
            if (data.BZJGIsNull)
            {
                commandParameters[7].Value = DBNull.Value;
            }
            else
            {
                commandParameters[7].Value = data.BZJG;
            }
            commandParameters[8] = new SqlParameter("@DeadLine", SqlDbType.NVarChar, 50);
            if (data.DeadLineIsNull)
            {
                commandParameters[8].Value = DBNull.Value;
            }
            else
            {
                commandParameters[8].Value = data.DeadLine;
            }
            commandParameters[9] = new SqlParameter("@Secret", SqlDbType.NVarChar, 20);
            if (data.SecretIsNull)
            {
                commandParameters[9].Value = DBNull.Value;
            }
            else
            {
                commandParameters[9].Value = data.Secret;
            }
            commandParameters[10] = new SqlParameter("@CategoryNo", SqlDbType.NVarChar, 100);
            if (data.CategoryNoIsNull)
            {
                commandParameters[10].Value = DBNull.Value;
            }
            else
            {
                commandParameters[10].Value = data.CategoryNo;
            }
            commandParameters[11] = new SqlParameter("@CatalogNo", SqlDbType.NVarChar, 100);
            if (data.CatalogNoIsNull)
            {
                commandParameters[11].Value = DBNull.Value;
            }
            else
            {
                commandParameters[11].Value = data.CatalogNo;
            }
            commandParameters[12] = new SqlParameter("@ArchiveNo", SqlDbType.NVarChar, 100);
            if (data.ArchiveNoIsNull)
            {
                commandParameters[12].Value = DBNull.Value;
            }
            else
            {
                commandParameters[12].Value = data.ArchiveNo;
            }
            commandParameters[13] = new SqlParameter("@BoxNo", SqlDbType.NVarChar, 100);
            if (data.BoxNoIsNull)
            {
                commandParameters[13].Value = DBNull.Value;
            }
            else
            {
                commandParameters[13].Value = data.BoxNo;
            }
            commandParameters[14] = new SqlParameter("@MicroNo", SqlDbType.NVarChar, 100);
            if (data.MicroNoIsNull)
            {
                commandParameters[14].Value = DBNull.Value;
            }
            else
            {
                commandParameters[14].Value = data.MicroNo;
            }
            commandParameters[15] = new SqlParameter("@CertificateKind", SqlDbType.NVarChar, 20);
            if (data.CertificateKindIsNull)
            {
                commandParameters[15].Value = DBNull.Value;
            }
            else
            {
                commandParameters[15].Value = data.CertificateKind;
            }
            commandParameters[0x10] = new SqlParameter("@CertificateStart", SqlDbType.NVarChar, 200);
            if (data.CertificateStartIsNull)
            {
                commandParameters[0x10].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x10].Value = data.CertificateStart;
            }
            commandParameters[0x11] = new SqlParameter("@CertificateEnd", SqlDbType.NVarChar, 200);
            if (data.CertificateEndIsNull)
            {
                commandParameters[0x11].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x11].Value = data.CertificateEnd;
            }
            commandParameters[0x12] = new SqlParameter("@RollPage", SqlDbType.NVarChar, 200);
            if (data.RollPageIsNull)
            {
                commandParameters[0x12].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x12].Value = data.RollPage;
            }
            commandParameters[0x13] = new SqlParameter("@Remark", SqlDbType.NVarChar, 200);
            if (data.RemarkIsNull)
            {
                commandParameters[0x13].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x13].Value = data.Remark;
            }
            commandParameters[20] = new SqlParameter("@ManageUser", SqlDbType.NText);
            if (data.ManageUserIsNull)
            {
                commandParameters[20].Value = DBNull.Value;
            }
            else
            {
                commandParameters[20].Value = data.ManageUser;
            }
            commandParameters[0x15] = new SqlParameter("@ReadUser", SqlDbType.NText);
            if (data.ReadUserIsNull)
            {
                commandParameters[0x15].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x15].Value = data.ReadUser;
            }
            commandParameters[0x16] = new SqlParameter("@AddUser", SqlDbType.NVarChar, 20);
            commandParameters[0x16].Value = data.AddUser;
            commandParameters[0x17] = new SqlParameter("@AddTime", SqlDbType.DateTime);
            commandParameters[0x17].Value = data.AddTime;
            commandParameters[0x18] = new SqlParameter("@ModUser", SqlDbType.NVarChar, 20);
            if (data.ModUserIsNull)
            {
                commandParameters[0x18].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x18].Value = data.ModUser;
            }
            commandParameters[0x19] = new SqlParameter("@ModTime", SqlDbType.DateTime);
            if (data.ModTimeIsNull)
            {
                commandParameters[0x19].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x19].Value = data.ModTime;
            }
            commandParameters[0x1a] = new SqlParameter("@DelUser", SqlDbType.NVarChar, 20);
            if (data.DelUserIsNull)
            {
                commandParameters[0x1a].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x1a].Value = data.DelUser;
            }
            commandParameters[0x1b] = new SqlParameter("@DelTime", SqlDbType.DateTime);
            if (data.DelTimeIsNull)
            {
                commandParameters[0x1b].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x1b].Value = data.DelTime;
            }
            commandParameters[0x1c] = new SqlParameter("@Borrow", SqlDbType.Int);
            commandParameters[0x1c].Value = data.Borrow;
            commandParameters[0x1d] = new SqlParameter("@Status", SqlDbType.Int);
            commandParameters[0x1d].Value = data.Status;
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, builder.ToString(), commandParameters);
            if (obj2 != DBNull.Value)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public DataTable SelectByRollId(SqlConnection conn, int rollId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[Rms_Roll].*, ");
            builder.Append("\t[Rms_Room].[RoomName], ");
            builder.Append("\tdbo.GetDeptNames(CAST([Rms_Roll].[DeptId] AS nvarchar(20))) AS DeptName ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Rms_Roll], ");
            builder.Append("\t[dbo].[Rms_Room] ");
            builder.Append("WHERE ");
            builder.Append("\t[Rms_Roll].[RollId] = @RollId ");
            builder.Append("\tAND [Rms_Roll].[RoomId] = [Rms_Room].[RoomId] ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@RollId", SqlDbType.Int) };
            commandParameters[0].Value = rollId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Rms_Roll";
            return set.Tables[0];
        }

        public DataTable SelectOpen(SqlConnection conn)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[RollId], ");
            builder.Append("\t[RollCode] + ' - ' + [RollName] AS RollName ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Rms_Roll] ");
            builder.Append("WHERE ");
            builder.Append("\t[Status] = 0 ");
            builder.Append("ORDER BY ");
            builder.Append("\t[RollId] ASC ");
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString());
            set.Tables[0].TableName = "Rms_Roll";
            return set.Tables[0];
        }

        public DataTable SelectPaged(SqlConnection conn, int pagesize, int pageindex, bool docount)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("IF(@docount=1) ");
            builder.Append("\tSELECT COUNT([RollId]) FROM [dbo].[Rms_Roll] ");
            builder.Append("ELSE ");
            builder.Append("\tBEGIN ");
            builder.Append("\t\tDECLARE @IndexTable TABLE(ID int identity(1,1),PK int) ");
            builder.Append("\t\tDECLARE @PageLowerBound int ");
            builder.Append("\t\tDECLARE @PageUpperBound int ");
            builder.Append("\t\tSET @PageLowerBound=(@pageindex-1)*@pagesize ");
            builder.Append("\t\tSET @PageUpperBound=@PageLowerBound+@pagesize ");
            builder.Append("\t\tSET rowcount @PageUpperBound ");
            builder.Append("\t\t ");
            builder.Append("\t\tINSERT INTO @IndexTable(PK) SELECT [RollId] FROM [dbo].[Rms_Roll] ");
            builder.Append("\t\t ");
            builder.Append("\t\tSELECT ");
            builder.Append("\t\t\t[Rms_Roll].[RollId], ");
            builder.Append("\t\t\t[Rms_Roll].[RollCode], ");
            builder.Append("\t\t\t[Rms_Roll].[RollName], ");
            builder.Append("\t\t\t[Rms_Roll].[CategoryNo], ");
            builder.Append("\t\t\t[Rms_Roll].[CertificateKind], ");
            builder.Append("\t\t\t[Rms_Roll].[Secret], ");
            builder.Append("\t\t\t[Rms_Roll].[Status], ");
            builder.Append("\t\t\t[Rms_Room].[RoomName], ");
            builder.Append("\t\t\t@pageindex AS pageindex ");
            builder.Append("\t\tFROM ");
            builder.Append("\t\t\t[dbo].[Rms_Roll], ");
            builder.Append("\t\t\t[dbo].[Rms_Room], ");
            builder.Append("\t\t\t@IndexTable t ");
            builder.Append("\t\tWHERE ");
            builder.Append("\t\t\t[Rms_Roll].[RoomId] = [Rms_Room].[RoomId] ");
            builder.Append("\t\t\tAND [Rms_Roll].[RollId] = t.PK\t\t\t ");
            builder.Append("\t\t\tAND t.ID>@PageLowerBound  ");
            builder.Append("\t\t\tAND t.ID<=@PageUpperBound ");
            builder.Append("\tEND ");
            SqlParameter[] commandParameters = new SqlParameter[3];
            commandParameters[0] = new SqlParameter("@pagesize", SqlDbType.Int);
            commandParameters[0].Value = pagesize;
            commandParameters[1] = new SqlParameter("@pageindex", SqlDbType.Int);
            commandParameters[1].Value = pageindex;
            commandParameters[2] = new SqlParameter("@docount", SqlDbType.Bit);
            commandParameters[2].Value = docount;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Rms_Roll";
            return set.Tables[0];
        }

        public bool Status(SqlConnection conn, int rollId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DECLARE @Status\tint ");
            builder.Append("SELECT ");
            builder.Append("\t@Status = [Status]  ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Rms_Roll] ");
            builder.Append("WHERE ");
            builder.Append("\t[RollId] = @RollId ");
            builder.Append("IF @Status = 0 ");
            builder.Append("\tSET @Status =1 ");
            builder.Append("ELSE ");
            builder.Append("\tSET @Status =0 ");
            builder.Append("UPDATE [dbo].[Rms_Roll] ");
            builder.Append("SET ");
            builder.Append("\t[Status] = @Status  ");
            builder.Append("WHERE ");
            builder.Append("\t[RollId] = @RollId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@RollId", SqlDbType.Int) };
            commandParameters[0].Value = rollId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public bool Update(SqlConnection conn, RmsRollData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE [dbo].[Rms_Roll] SET ");
            builder.Append("\t[RoomId] = @RoomId, ");
            builder.Append("\t[RollCode] = @RollCode, ");
            builder.Append("\t[RollName] = @RollName, ");
            builder.Append("\t[Years] = @Years, ");
            builder.Append("\t[BeginDate] = @BeginDate, ");
            builder.Append("\t[EndDate] = @EndDate, ");
            builder.Append("\t[DeptId] = @DeptId, ");
            builder.Append("\t[BZJG] = @BZJG, ");
            builder.Append("\t[DeadLine] = @DeadLine, ");
            builder.Append("\t[Secret] = @Secret, ");
            builder.Append("\t[CategoryNo] = @CategoryNo, ");
            builder.Append("\t[CatalogNo] = @CatalogNo, ");
            builder.Append("\t[ArchiveNo] = @ArchiveNo, ");
            builder.Append("\t[BoxNo] = @BoxNo, ");
            builder.Append("\t[MicroNo] = @MicroNo, ");
            builder.Append("\t[CertificateKind] = @CertificateKind, ");
            builder.Append("\t[CertificateStart] = @CertificateStart, ");
            builder.Append("\t[CertificateEnd] = @CertificateEnd, ");
            builder.Append("\t[RollPage] = @RollPage, ");
            builder.Append("\t[Remark] = @Remark, ");
            builder.Append("\t[ModUser] = @ModUser, ");
            builder.Append("\t[ModTime] = @ModTime ");
            builder.Append("WHERE ");
            builder.Append("\t[RollId] = @RollId ");
            SqlParameter[] commandParameters = new SqlParameter[0x1f];
            commandParameters[0] = new SqlParameter("@RollId", SqlDbType.Int);
            commandParameters[0].Value = data.RollId;
            commandParameters[1] = new SqlParameter("@RoomId", SqlDbType.Int);
            commandParameters[1].Value = data.RoomId;
            commandParameters[2] = new SqlParameter("@RollCode", SqlDbType.NVarChar, 200);
            commandParameters[2].Value = data.RollCode;
            commandParameters[3] = new SqlParameter("@RollName", SqlDbType.NVarChar, 200);
            commandParameters[3].Value = data.RollName;
            commandParameters[4] = new SqlParameter("@Years", SqlDbType.NVarChar, 50);
            if (data.YearsIsNull)
            {
                commandParameters[4].Value = DBNull.Value;
            }
            else
            {
                commandParameters[4].Value = data.Years;
            }
            commandParameters[5] = new SqlParameter("@BeginDate", SqlDbType.DateTime);
            if (data.BeginDateIsNull)
            {
                commandParameters[5].Value = DBNull.Value;
            }
            else
            {
                commandParameters[5].Value = data.BeginDate;
            }
            commandParameters[6] = new SqlParameter("@EndDate", SqlDbType.DateTime);
            if (data.EndDateIsNull)
            {
                commandParameters[6].Value = DBNull.Value;
            }
            else
            {
                commandParameters[6].Value = data.EndDate;
            }
            commandParameters[7] = new SqlParameter("@DeptId", SqlDbType.Int);
            if (data.DeptIdIsNull)
            {
                commandParameters[7].Value = DBNull.Value;
            }
            else
            {
                commandParameters[7].Value = data.DeptId;
            }
            commandParameters[8] = new SqlParameter("@BZJG", SqlDbType.NVarChar, 100);
            if (data.BZJGIsNull)
            {
                commandParameters[8].Value = DBNull.Value;
            }
            else
            {
                commandParameters[8].Value = data.BZJG;
            }
            commandParameters[9] = new SqlParameter("@DeadLine", SqlDbType.NVarChar, 50);
            if (data.DeadLineIsNull)
            {
                commandParameters[9].Value = DBNull.Value;
            }
            else
            {
                commandParameters[9].Value = data.DeadLine;
            }
            commandParameters[10] = new SqlParameter("@Secret", SqlDbType.NVarChar, 20);
            if (data.SecretIsNull)
            {
                commandParameters[10].Value = DBNull.Value;
            }
            else
            {
                commandParameters[10].Value = data.Secret;
            }
            commandParameters[11] = new SqlParameter("@CategoryNo", SqlDbType.NVarChar, 100);
            if (data.CategoryNoIsNull)
            {
                commandParameters[11].Value = DBNull.Value;
            }
            else
            {
                commandParameters[11].Value = data.CategoryNo;
            }
            commandParameters[12] = new SqlParameter("@CatalogNo", SqlDbType.NVarChar, 100);
            if (data.CatalogNoIsNull)
            {
                commandParameters[12].Value = DBNull.Value;
            }
            else
            {
                commandParameters[12].Value = data.CatalogNo;
            }
            commandParameters[13] = new SqlParameter("@ArchiveNo", SqlDbType.NVarChar, 100);
            if (data.ArchiveNoIsNull)
            {
                commandParameters[13].Value = DBNull.Value;
            }
            else
            {
                commandParameters[13].Value = data.ArchiveNo;
            }
            commandParameters[14] = new SqlParameter("@BoxNo", SqlDbType.NVarChar, 100);
            if (data.BoxNoIsNull)
            {
                commandParameters[14].Value = DBNull.Value;
            }
            else
            {
                commandParameters[14].Value = data.BoxNo;
            }
            commandParameters[15] = new SqlParameter("@MicroNo", SqlDbType.NVarChar, 100);
            if (data.MicroNoIsNull)
            {
                commandParameters[15].Value = DBNull.Value;
            }
            else
            {
                commandParameters[15].Value = data.MicroNo;
            }
            commandParameters[0x10] = new SqlParameter("@CertificateKind", SqlDbType.NVarChar, 20);
            if (data.CertificateKindIsNull)
            {
                commandParameters[0x10].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x10].Value = data.CertificateKind;
            }
            commandParameters[0x11] = new SqlParameter("@CertificateStart", SqlDbType.NVarChar, 200);
            if (data.CertificateStartIsNull)
            {
                commandParameters[0x11].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x11].Value = data.CertificateStart;
            }
            commandParameters[0x12] = new SqlParameter("@CertificateEnd", SqlDbType.NVarChar, 200);
            if (data.CertificateEndIsNull)
            {
                commandParameters[0x12].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x12].Value = data.CertificateEnd;
            }
            commandParameters[0x13] = new SqlParameter("@RollPage", SqlDbType.NVarChar, 200);
            if (data.RollPageIsNull)
            {
                commandParameters[0x13].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x13].Value = data.RollPage;
            }
            commandParameters[20] = new SqlParameter("@Remark", SqlDbType.NVarChar, 200);
            if (data.RemarkIsNull)
            {
                commandParameters[20].Value = DBNull.Value;
            }
            else
            {
                commandParameters[20].Value = data.Remark;
            }
            commandParameters[0x15] = new SqlParameter("@ManageUser", SqlDbType.NText);
            if (data.ManageUserIsNull)
            {
                commandParameters[0x15].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x15].Value = data.ManageUser;
            }
            commandParameters[0x16] = new SqlParameter("@ReadUser", SqlDbType.NText);
            if (data.ReadUserIsNull)
            {
                commandParameters[0x16].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x16].Value = data.ReadUser;
            }
            commandParameters[0x17] = new SqlParameter("@AddUser", SqlDbType.NVarChar, 20);
            commandParameters[0x17].Value = data.AddUser;
            commandParameters[0x18] = new SqlParameter("@AddTime", SqlDbType.DateTime);
            commandParameters[0x18].Value = data.AddTime;
            commandParameters[0x19] = new SqlParameter("@ModUser", SqlDbType.NVarChar, 20);
            if (data.ModUserIsNull)
            {
                commandParameters[0x19].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x19].Value = data.ModUser;
            }
            commandParameters[0x1a] = new SqlParameter("@ModTime", SqlDbType.DateTime);
            if (data.ModTimeIsNull)
            {
                commandParameters[0x1a].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x1a].Value = data.ModTime;
            }
            commandParameters[0x1b] = new SqlParameter("@DelUser", SqlDbType.NVarChar, 20);
            if (data.DelUserIsNull)
            {
                commandParameters[0x1b].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x1b].Value = data.DelUser;
            }
            commandParameters[0x1c] = new SqlParameter("@DelTime", SqlDbType.DateTime);
            if (data.DelTimeIsNull)
            {
                commandParameters[0x1c].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x1c].Value = data.DelTime;
            }
            commandParameters[0x1d] = new SqlParameter("@Borrow", SqlDbType.Int);
            commandParameters[0x1d].Value = data.Borrow;
            commandParameters[30] = new SqlParameter("@Status", SqlDbType.Int);
            commandParameters[30].Value = data.Status;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }
    }
}

